package moddb;

import java.sql.*;
import java.util.regex.*;
import java.util.LinkedList;
import moddb.schema.*;

public class Middletier {
  
  public static boolean registerUser(String username, String password) {
    
    try {
      Connection con = Record.getRecord("jdbc:sqlite:users.sq3").getConnection();
      PreparedStatement insertUser = con.prepareStatement("INSERT INTO users (username, pwd) VALUES (?, ?)");
      PreparedStatement insertUserRole = con.prepareStatement("INSERT INTO user_roles (user_id, role_id) VALUES (?, (SELECT id FROM roles WHERE role = \"user\"))");
      
      String sha1 = AeSimpleSHA1.SHA1("SALTar" + password);
      
      insertUser.setString(1, username);
      insertUser.setString(2, sha1);
      insertUser.executeUpdate();
      
      ResultSet rs = insertUser.getGeneratedKeys();
      
      if (rs.next()) {
        insertUserRole.setInt(1, rs.getInt(1));
        rs.close();
      } else {
        rs.close();
        throw new SQLException("Could not get inserted user key");
      }
      
      insertUserRole.executeUpdate();
      
      return true;
      
    } catch (SQLException e) {
      e.printStackTrace();
      return false;
    } catch (java.security.NoSuchAlgorithmException e) {
      System.err.println("Missing SHA1, not able to register user!");
      return false;
    } catch (java.io.UnsupportedEncodingException e) {
      System.err.println("Failed during SHA1 on password, not able to register user!");
       return false;
    }
    
  }
  
  public static LinkedList<File> getFiles(String userQuery, String fileQuery, int limit, int offset) {

    try {
      Connection con = Record.getRecord("jdbc:sqlite:users.sq3").getConnection();
      ResultSet files = con.createStatement().executeQuery("SELECT files.name,users.username,files.id,users.id from users, files WHERE files.user_id = users.id LIMIT " + limit + " OFFSET " + offset);
      LinkedList<File> result = new LinkedList<File>();
      
      Pattern usernamePattern = Pattern.compile(userQuery);
      Pattern filenamePattern = Pattern.compile(fileQuery);
      
      System.err.println("Filename: " + filenamePattern.pattern());
      System.err.println("Username: " + usernamePattern.pattern());
      
      while (files.next()) {
        String filename = files.getString(1);
        String username = files.getString(2);
        
        System.err.println("Mathing filename: " + filename + " against: " + filenamePattern.pattern());
        System.err.println("Mathing username: " + username + " against: " + usernamePattern.pattern());
        
        // && => files depends on user => /users/.*/files/.* (nested)
        if (usernamePattern.matcher(username).find() && filenamePattern.matcher(filename).find()) {
          System.err.println("matched!");
          result.addLast(new File(files.getString(3), filename, username));
        }
      }
      
      files.close();
      
      return result;

    } catch (SQLException e) {
      e.printStackTrace();
      return null;
    }
    
  }
  
  public static byte[] getFileData(String username, int fileId) {
    
    try {
      Connection con = Record.getRecord("jdbc:sqlite:users.sq3").getConnection();
      PreparedStatement selectFileData = con.prepareStatement("SELECT data FROM files,users WHERE files.user_id = users.id AND files.id = ? AND users.username = ?");
      byte[] data = null;
      
      selectFileData.setInt(1, fileId);
      selectFileData.setString(2, username);
      
      ResultSet rs = selectFileData.executeQuery();
      
      if (rs.next()) {
        data = rs.getBytes(1);
      }
      
      rs.close();
      return data;
      
    } catch (SQLException e) {
      e.printStackTrace();
      return null;
    }
    
  }
  
  public static boolean uploadFile(String username, String filename, byte[] data) {
    
    try {
      Connection con = Record.getRecord("jdbc:sqlite:users.sq3").getConnection();
      PreparedStatement insertFile = con.prepareStatement("INSERT INTO files (name, user_id, data) VALUES (?, (SELECT id FROM users WHERE username = ?), ?)");
      
      insertFile.setString(1, filename);
      insertFile.setString(2, username);
      insertFile.setBytes(3, data);
      
      insertFile.executeUpdate();
      return true;
      
    } catch (SQLException e) {
      e.printStackTrace();
      return false;
    }
  }
 
  public static boolean deleteFile(int fileId) {
    
    try {
      Connection con = Record.getRecord("jdbc:sqlite:users.sq3").getConnection();
      PreparedStatement deleteFile = con.prepareStatement("DELETE FROM files WHERE id = ?");
      
      deleteFile.setInt(1, fileId);
      
      deleteFile.executeUpdate();
      return true;
      
    } catch (SQLException e) {
      e.printStackTrace();
      return false;
    }
  }
}
